package com.iplant.mes.serviceImpl.dao.sfc;

import com.iplant.base.po.OutResult;
import com.iplant.base.po.enums.ExceptionTypes;
import com.iplant.base.utils.annotation.dao.DBBaseDAO;
import com.iplant.base.utils.cache.CacheUtils;
import com.iplant.base.utils.general.StringUtils;
import com.iplant.base.utils.sql.BaseDAO;
import com.iplant.basic.service.basicenum.stat.StatTypes;
import com.iplant.basic.service.po.bms.BMSEmployee;
import com.iplant.basic.service.po.fmc.FMCStation;
import com.iplant.mes.service.mesenum.sfc.SFCTaskStatus;
import com.iplant.mes.service.po.qms.QMSPassStationRate;
import com.iplant.mes.service.po.sfc.SFCTaskReport;
import com.iplant.pdm.service.basicenum.pdm.PDMComponentTypes;
import lombok.extern.slf4j.Slf4j;

import java.util.*;
import java.util.stream.Collectors;

@DBBaseDAO(SFCTaskReport.class)
@Slf4j
public class SFCTaskReportDAO extends BaseDAO {

    private static SFCTaskReportDAO Instance = null;

    public static SFCTaskReportDAO getInstance() {
        if (Instance == null) {
            Instance = new SFCTaskReportDAO();
        }
        return Instance;
    }

    public SFCTaskReportDAO() {
        super("");
        this.Init();
    }


    /**
     * 工序趋势统计
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo
     * @param wStatType
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassStationRate> QMS_GetPassStationRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID, String wOrderNo, int wStatType,
                                                               Calendar wStartDate, Calendar wEndDate, OutResult<Integer> wErrorCode) {
        List<QMSPassStationRate> wResult = new ArrayList<>();
        try {
            StatTypes wType= StatTypes.getEnumType(wStatType);
            String wGroupType="";
            switch (wType)
            {
                case Week:
                    wGroupType = " str_to_date( DATE_FORMAT(t.StationDate,'%Y-%u-1'),'%Y-%u-%w') ";

                    break;
                case  Month:
                    wGroupType = " str_to_date( DATE_FORMAT(t.StationDate,'%Y-%m-1'),'%Y-%m-%d') ";

                    break;
                case Quarter:
                    wGroupType = " str_to_date( concat(  year(t.StationDate),'-',(quarter(t.StationDate) * 3)-2,'-1'),'%Y-%m-%d') ";

                    break;
                case Year:
                    wGroupType = " str_to_date( concat(  year(t.StationDate),'-01','-01'),'%Y-%m-%d')  ";

                    break;
                default:
                    wGroupType = " str_to_date( DATE_FORMAT(t.StationDate,'%Y-%m-%d'),'%Y-%m-%d') ";
                    break;
            }

            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());

            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText= StringUtils.Format("SELECT t.RoutePartPointName,  {1}  as StatTypeDate," +
                    "  sum(t.ActNum) as GoodFQTY,  sum(t.BadNum) as BadFQTY " +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wStartDate <= t.StationDate) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.StationDate)  " +
                    "  GROUP BY t.RoutePartPointName, StatTypeDate", getTableName(),wGroupType);

            List<Map<String, Object>> wQueryResult =  this.queryForList(wSQLText,wParams);

            if(wQueryResult==null||wQueryResult.size() == 0)
                return wResult;

            QMSPassStationRate wQMSPassStationRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassStationRate=new QMSPassStationRate();
                wQMSPassStationRate.PartPointName=StringUtils.parseString(wReader.get("RoutePartPointName"));
                wQMSPassStationRate.RateDate=StringUtils.parseCalendar(wReader.get("StatTypeDate"));
                wQMSPassStationRate.GoodFQTY=StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassStationRate.BadFQTY=StringUtils.parseInt(wReader.get("BadFQTY"));


                wResult.add(wQMSPassStationRate);
            }

        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetPassStationRateByList", ex);
        }
        return wResult;
    }

    /**
     * 工位趋势统计
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo
     * @param wStatType
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassStationRate> QMS_GetStationPassStationRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID, String wOrderNo, int wStatType,
                                                                      Calendar wStartDate, Calendar wEndDate, OutResult<Integer> wErrorCode) {
        List<QMSPassStationRate> wResult = new ArrayList<>();
        try {
            StatTypes wType= StatTypes.getEnumType(wStatType);
            String wGroupType="";
            switch (wType)
            {
                case Week:
                    wGroupType = " str_to_date( DATE_FORMAT(t.StationDate,'%Y-%u-1'),'%Y-%u-%w') ";

                    break;
                case  Month:
                    wGroupType = " str_to_date( DATE_FORMAT(t.StationDate,'%Y-%m-1'),'%Y-%m-%d') ";

                    break;
                case Quarter:
                    wGroupType = " str_to_date( concat(  year(t.StationDate),'-',(quarter(t.StationDate) * 3)-2,'-1'),'%Y-%m-%d') ";

                    break;
                case Year:
                    wGroupType = " str_to_date( concat(  year(t.StationDate),'-01','-01'),'%Y-%m-%d')  ";

                    break;
                default:
                    wGroupType = " str_to_date( DATE_FORMAT(t.StationDate,'%Y-%m-%d'),'%Y-%m-%d') ";
                    break;
            }

            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());
            wParams.put("wProductComType", PDMComponentTypes.Product.getValue());
            wParams.put("wComType", PDMComponentTypes.Component.getValue());
            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText= StringUtils.Format("SELECT t.RoutePartPointName,t.StationID,  {1}  as StatTypeDate," +
                    "  sum(t.ActNum) as GoodFQTY,  sum(t.BadNum) as BadFQTY " +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t.StationDate) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.StationDate)  " +
                    "  GROUP BY t.StationID,t.RoutePartPointName, StatTypeDate",  getTableName(),wGroupType);


            List<Map<String, Object>> wQueryResult =  this.queryForList(wSQLText,wParams);

            if(wQueryResult==null||wQueryResult.size() == 0)
                return wResult;

            Map<Integer, FMCStation> wFMCStationMap = CacheUtils.GetCacheTList(FMCStation.class).stream().collect(Collectors.toMap(p -> p.ID, p -> p, (o1, o2) -> o1.Active == 1 ? o1 : o2));
            QMSPassStationRate wQMSPassStationRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassStationRate=new QMSPassStationRate();
                wQMSPassStationRate.StationID=StringUtils.parseInt(wReader.get("StationID"));
                wQMSPassStationRate.PartPointName=StringUtils.parseString(wReader.get("RoutePartPointName"));
                wQMSPassStationRate.RateDate=StringUtils.parseCalendar(wReader.get("StatTypeDate"));
                wQMSPassStationRate.GoodFQTY=StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassStationRate.BadFQTY=StringUtils.parseInt(wReader.get("BadFQTY"));

                if(wFMCStationMap.containsKey(wQMSPassStationRate.StationID)){
                    wQMSPassStationRate.StationName=wFMCStationMap.get(wQMSPassStationRate.StationID).Name;
                    wQMSPassStationRate.StationCode=wFMCStationMap.get(wQMSPassStationRate.StationID).Code;
                }
                wResult.add(wQMSPassStationRate);
            }
        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetProductPassStationRateByList", ex);
        }
        return wResult;
    }


    /**
     * 工序统计
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo 
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassStationRate> QMS_GetPassStationRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID, String wOrderNo,
                                                               Calendar wStartDate, Calendar wEndDate, OutResult<Integer> wErrorCode) {
        List<QMSPassStationRate> wResult = new ArrayList<>();
        try {


            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());

            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText= StringUtils.Format("SELECT t.RoutePartPointName," +
                    "  sum(t.ActNum) as GoodFQTY,  sum(t.BadNum) as BadFQTY " +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t.StationDate) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.StationDate)  " +
                    "  GROUP BY t.RoutePartPointName", getTableName());

            List<Map<String, Object>> wQueryResult =  this.queryForList(wSQLText,wParams);

            if(wQueryResult==null||wQueryResult.size() == 0)
                return wResult;

            QMSPassStationRate wQMSPassStationRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassStationRate=new QMSPassStationRate();
                wQMSPassStationRate.PartPointName=StringUtils.parseString(wReader.get("RoutePartPointName"));
                wQMSPassStationRate.GoodFQTY=StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassStationRate.BadFQTY=StringUtils.parseInt(wReader.get("BadFQTY"));


                wResult.add(wQMSPassStationRate);
            }

        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetPassStationRateByList", ex);
        }
        return wResult;
    }

    /**
     * 工位统计
     * @param wLoginUser
     * @param wProductID
     * @param wOrderID
     * @param wOrderNo 
     * @param wStartDate
     * @param wEndDate
     * @param wErrorCode
     * @return
     */
    public List<QMSPassStationRate> QMS_GetStationPassStationRateList(BMSEmployee wLoginUser, int wProductID, int wOrderID, String wOrderNo,
                                                                      Calendar wStartDate, Calendar wEndDate, OutResult<Integer> wErrorCode) {
        List<QMSPassStationRate> wResult = new ArrayList<>();
        try {


            Map<String, Object> wParams = new HashMap<>();
            wParams.put("wFinishStatus", SFCTaskStatus.Done.getValue());
            wParams.put("wScrapStatus", SFCTaskStatus.Scarp.getValue());
            wParams.put("wProductComType", PDMComponentTypes.Product.getValue());
            wParams.put("wComType", PDMComponentTypes.Component.getValue());
            wParams.put("wProductID", wProductID);
            wParams.put("wOrderID", wOrderID);
            wParams.put("wOrderNo", wOrderNo);
            wParams.put("wStartDate", wStartDate);
            wParams.put("wEndDate", wEndDate);

            String wSQLText= StringUtils.Format("SELECT t.RoutePartPointName,t.StationID, " +
                    "  sum(t.ActNum) as GoodFQTY,  sum(t.BadNum) as BadFQTY" +
                    "  FROM {0} t where ID>0 and (:wProductID<=0 or t.ProductID=:wProductID) " +
                    "  and (:wOrderID<=0 or t.OrderID=:wOrderID)" +
                    "  and (:wOrderNo = '''' or t.OrderNo=:wOrderNo) " +
                    "  and (:wStartDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartDate <= t.StationDate) " +
                    "  and (:wEndDate <= str_to_date(''2010-01-01'', ''%Y-%m-%d'') or :wEndDate >= t.StationDate)  " +
                    "  GROUP BY t.StationID,t.RoutePartPointName",  getTableName());


            List<Map<String, Object>> wQueryResult =  this.queryForList(wSQLText,wParams);

            if(wQueryResult==null||wQueryResult.size() == 0)
                return wResult;

            Map<Integer, FMCStation> wFMCStationMap = CacheUtils.GetCacheTList(FMCStation.class).stream().collect(Collectors.toMap(p -> p.ID, p -> p, (o1, o2) -> o1.Active == 1 ? o1 : o2));
            QMSPassStationRate wQMSPassStationRate;
            for (Map<String, Object> wReader : wQueryResult) {
                wQMSPassStationRate=new QMSPassStationRate();
                wQMSPassStationRate.StationID=StringUtils.parseInt(wReader.get("StationID"));
                wQMSPassStationRate.PartPointName=StringUtils.parseString(wReader.get("RoutePartPointName"));
                wQMSPassStationRate.GoodFQTY=StringUtils.parseInt(wReader.get("GoodFQTY"));
                wQMSPassStationRate.BadFQTY=StringUtils.parseInt(wReader.get("BadFQTY"));

                if(wFMCStationMap.containsKey(wQMSPassStationRate.StationID)){
                    wQMSPassStationRate.StationName=wFMCStationMap.get(wQMSPassStationRate.StationID).Name;
                    wQMSPassStationRate.StationCode=wFMCStationMap.get(wQMSPassStationRate.StationID).Code;
                }
                wResult.add(wQMSPassStationRate);
            }
        } catch (Exception ex) {
            wErrorCode.Result = ExceptionTypes.DBSQL.getValue();
            log.error("QMS_GetProductPassStationRateByList", ex);
        }
        return wResult;
    }
}
